/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package dbase;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import model.*;

/**
 *
 * @author aa
 */
public class DBcari {
    Connection conn;

    public DBcari(Connection conn){
        this.conn = conn;
    }

    public List<keanggotaan> getCari(String input, String input2) throws Exception {
        List<keanggotaan> kendAng = new ArrayList<keanggotaan>();
        String sql = " SELECT * FROM kendaraan JOIN anggota ON kendaraan.nrp = anggota.nrp WHERE anggota.nrp LIKE ? OR anggota.nama LIKE ?";
        PreparedStatement st = this.conn.prepareStatement(sql);
        st.setString(1, "%"+ input +"%");
        st.setString(2, "%"+ input2 +"%");
        ResultSet rs = st.executeQuery();
        while(rs.next()){
            keanggotaan  kendaraanData= new keanggotaan();
            kendaraanData.setIdKendaraan(rs.getInt(1));
            kendaraanData.setNoPolisi(rs.getString(2));
            kendaraanData.setNoRangka(rs.getString(3));
            kendaraanData.setNoMesin(rs.getString(4));
            kendaraanData.setNoSTNK(rs.getString(5));
            kendaraanData.setWarna(rs.getString(6));
            kendaraanData.setMerek(rs.getString(7));
            kendaraanData.setJenisMotor(rs.getString(8));
            kendaraanData.setNrp(rs.getString(9));
            kendaraanData.setIdAnggota(rs.getInt(10));
            kendaraanData.setNrp(rs.getString(12));
            kendaraanData.setNama(rs.getString(13));
            kendaraanData.setStatusAnggota(rs.getString(14));
            kendaraanData.setAlamat(rs.getString(15));
            kendAng.add(kendaraanData);
        }
        return kendAng;
    }

    public void insertKartu() throws Exception {
        String sql = "INSERT INTO karturfid (id_kartu, tgl_pembuatan) SELECT CONCAT('RFP',(MAX(SUBSTRING(id_kartu,4))+1)), DATE(NOW()) FROM karturfid";
        PreparedStatement st = conn.prepareStatement(sql);
        st.executeUpdate();
        st.close();
    }

   public void insertRfidMotor() throws Exception {
        String sql = "INSERT INTO rfidmotor (id_rfid, tgl_pembuatan2) SELECT CONCAT('RFM',(MAX(SUBSTRING(id_rfid,4))+1)), DATE(NOW()) FROM rfidmotor";
        PreparedStatement st = conn.prepareStatement(sql);
        st.executeUpdate();
        st.close();
    }

    public void insertKeanggotaan(String nrp) throws Exception {
        String sql = "INSERT INTO keanggotaan(id_kartu,tgl_daftar,masa_berlaku,nrp,id_rfid)VALUES ((SELECT CONCAT('RFP',(MAX(SUBSTRING(id_kartu,4)))) FROM karturfid),DATE(NOW()),(SELECT CONCAT(YEAR(DATE(NOW()))+1,'-',MONTH(NOW()),'-',DAY(NOW()))),?,(SELECT CONCAT('RFM',(MAX(SUBSTRING(id_rfid,4))))FROM rfidmotor))";
        PreparedStatement st = conn.prepareStatement(sql);
        st.setString(1, nrp);
        st.executeUpdate();
        st.close();
    }

    public void updateStatusApprove (String nrp) throws Exception {
        String sql = "UPDATE anggota SET statusApprove='Sudah' WHERE nrp=?" ;
        try {
            PreparedStatement st = conn.prepareStatement(sql);
            st.setString(1, nrp);
            st.executeUpdate();
            st.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


}
